package com.FH.applyFrom.work_week_plan_detail;

import com.FH.util.*;
import com.itextpdf.text.DocumentException;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

import javax.ejb.Stateless;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * Created by Administrator on 2017/11/22 0022.
 */
@Stateless
public class WorkWeekPlanDetailDAOImpl extends AccessDB implements WorkWeekPlanDetailDAO{
    /**
     * 每日计划查询
     *
     * @param search
     * @param page
     * @param size
     */
    @Override
    public ResultDTO getWorkWeekPlanDetailInfo(String search, String page, String size) {

        ResultDTO resultDTO = new ResultDTO();

        try {
            JSONObject json = new JSONObject(search);

            String sqlexit = "select ";

            String sql_w = "";//查询sql组合
            String sql_page;//分页sql组合
            Integer page_ok = Integer.parseInt(page);
            Integer size_ok = Integer.parseInt(size);
            if(page.equals("1")){
                sql_page = " limit 0," + size;
            }else {
                sql_page = " limit " + ((page_ok-1)*size_ok) + "," + size;
            }
            if (json.has("work_week_plan_detail_id")){
                sql_w += " and work_week_plan_detail_id = '"+json.getString("work_week_plan_detail_id")+"'";
            }
            if (json.has("work_week_plan_id")){
                sql_w += " and work_week_plan_id = '"+json.getString("work_week_plan_id")+"'";
            }

            String sql_ok = sqlexit+sql_w;
//            String existName = getReportingToOne(sql_ok);

            List<String[]> seresult = new ArrayList<String[]>();
            List<WorkWeekPlanDetailShowDTO> result = new ArrayList<WorkWeekPlanDetailShowDTO>();

//            List<List<WorkWeekPlanDetailShowDTO>> lresult = new ArrayList<List<WorkWeekPlanDetailShowDTO>>();

//            if(!existName.equals("0"))
//            {
                String sql = "select work_week_plan_detail_id,work_week_plan_id,week_date,year_date,am_plan,pm_plan," +
                        "create_time,update_time from work_week_plan_detail where 1=1" + sql_w + " order by year_date " + sql_page ;
                seresult = this.getReportingTo(sql);

                for (int i=0;i<seresult.size();i++){
                    WorkWeekPlanDetailShowDTO workWeekPlanDetailShowDTO = new WorkWeekPlanDetailShowDTO();

                    workWeekPlanDetailShowDTO.setWork_week_plan_detail_id(seresult.get(i)[1]);
                    workWeekPlanDetailShowDTO.setWork_week_plan_id(seresult.get(i)[2]);
                    workWeekPlanDetailShowDTO.setWeek_date(seresult.get(i)[3]);
                    workWeekPlanDetailShowDTO.setYear_date(seresult.get(i)[4]);
                    workWeekPlanDetailShowDTO.setAm_plan(seresult.get(i)[5]);
                    workWeekPlanDetailShowDTO.setPm_plan(seresult.get(i)[6]);
                    workWeekPlanDetailShowDTO.setCreate_time(seresult.get(i)[7]);
                    workWeekPlanDetailShowDTO.setUpdate_time(seresult.get(i)[8]);

                    result.add(workWeekPlanDetailShowDTO);
                }
                if (result.size() != 0) {
                    resultDTO.setStatus("90000");
                    resultDTO.setDescritpion(warnSrc.get_success);
                } else {
                    resultDTO.setStatus("90001");
                    resultDTO.setDescritpion(warnSrc.get_fail);
                }
                resultDTO.setStr(result.size() + "");
                resultDTO.setValues(result);
//            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return resultDTO;
    }

    /**
     * 每日计划添加
     *
     * @param workWeekPlanDetailPullDTO
     */
    @Override
    public ResultDTO addWorkWeekPlanDetailInfo(WorkWeekPlanDetailPullDTO workWeekPlanDetailPullDTO) {

        ResultDTO resultDTO = new ResultDTO();
        /*
         * 判断此信息是否已存在
         * 若存在,进行更新
         * 如不存在,执行add方法
         */
        String work_week_plan_detail_id = workWeekPlanDetailPullDTO.getWork_week_plan_detail_id();
        if(null!=work_week_plan_detail_id&&!"".equals(work_week_plan_detail_id)){
            return updateWorkWeekPlanDetailInfo(workWeekPlanDetailPullDTO);
        }

        String uuid = UUID.randomUUID().toString().trim().replaceAll("-", "");
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = df.format(new Date());
        String insert = "insert into work_week_plan_detail (work_week_plan_detail_id,work_week_plan_id,week_date,year_date,am_plan,pm_plan," +
                "create_time,update_time) values ('"+uuid+"','"+workWeekPlanDetailPullDTO.getWork_week_plan_id()+"','"+workWeekPlanDetailPullDTO.getWeek_date()+"','"+
                workWeekPlanDetailPullDTO.getYear_date()+"','"+workWeekPlanDetailPullDTO.getAm_plan()+"','"+workWeekPlanDetailPullDTO.getPm_plan()+"','"+
                time+"','"+time+"')";
        int result = addReportingTo(insert);
        if(result!=0){
            resultDTO.setStr(uuid);
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.add_success);
        } else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.add_fail);
        }
        return resultDTO;
    }

    /**
     * 每日计划修改
     *
     * @param workWeekPlanDetailPullDTO
     */
    @Override
    public ResultDTO updateWorkWeekPlanDetailInfo(WorkWeekPlanDetailPullDTO workWeekPlanDetailPullDTO) {

        ResultDTO resultDTO = new ResultDTO();

        //更新sql语句
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = df.format(new Date());
        String update = "update work_week_plan_detail set update_time ='" + time + "'";

        if (null != workWeekPlanDetailPullDTO.getWork_week_plan_id() && !"".equals(workWeekPlanDetailPullDTO.getWork_week_plan_id())) {
            update += ",work_week_plan_id ='" + workWeekPlanDetailPullDTO.getWork_week_plan_id() + "'";
        }
        if (null != workWeekPlanDetailPullDTO.getWeek_date() && !"".equals(workWeekPlanDetailPullDTO.getWeek_date())) {
            update += ",week_date ='" + workWeekPlanDetailPullDTO.getWeek_date() + "'";
        }
        if (null != workWeekPlanDetailPullDTO.getYear_date() && !"".equals(workWeekPlanDetailPullDTO.getYear_date())) {
            update += ",year_date ='" + workWeekPlanDetailPullDTO.getYear_date() + "'";
        }
        if (null != workWeekPlanDetailPullDTO.getAm_plan() && !"".equals(workWeekPlanDetailPullDTO.getAm_plan())) {
            update += ",am_plan ='" + workWeekPlanDetailPullDTO.getAm_plan() + "'";
        }
        if (null != workWeekPlanDetailPullDTO.getPm_plan() && !"".equals(workWeekPlanDetailPullDTO.getPm_plan())) {
            update += ",pm_plan ='" + workWeekPlanDetailPullDTO.getPm_plan() + "'";
        }

        update += " where work_week_plan_detail_id = '" + workWeekPlanDetailPullDTO.getWork_week_plan_detail_id() + "'";

        int result = addReportingTo(update);
        System.out.println(update);
        // 判断SQl是否执行成功
        if(result!=0){
            resultDTO.setStr(workWeekPlanDetailPullDTO.getWork_week_plan_detail_id());
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.update_success);
        } else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.update_fail);
        }
        return resultDTO;
    }

    /**
     * 每日计划删除
     *
     * @param workWeekPlanDetailPullDTO
     */
    @Override
    public ResultDTO deleteWorkWeekPlanDetailInfo(WorkWeekPlanDetailPullDTO workWeekPlanDetailPullDTO) {

        ResultDTO resultDTO = new ResultDTO();

        //判断是否存在
        String sql = "select count(1) from work_week_plan_detail where work_week_plan_detail_id = '"+workWeekPlanDetailPullDTO.getWork_week_plan_detail_id()+"'";
        String exist = getReportingToOne(sql);

        int result = 0;
        if(!exist.equals("0")){
            String delete = "delete from work_week_plan_detail where work_week_plan_detail_id = '"+workWeekPlanDetailPullDTO.getWork_week_plan_detail_id()+"'";
            result = addReportingTo(delete);
            System.out.println(delete);
        }
        // 判断SQl是否执行成功
        if (result != 0) {
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.delete_success);
        } else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.delete_fail);
        }
        return resultDTO;
    }

    @Override
    public ResultDTO getWorkWeekPlanDetail(String search) {

        ResultDTO resultDTO = new ResultDTO();
        try {
            JSONObject json = new JSONObject(search);

            String sqlOrgId = "SELECT DISTINCT w.organization_id,o.organization_name from work_week_plan w " +
                    "LEFT JOIN  organization o " +
                    "on o.organization_id = w.organization_id " +
                    "where 1=1 ";

            String start_time = "";
            if(json.has("start_time")){
                if(null != json.get("start_time").toString() && "" != json.get("start_time").toString()){
                    sqlOrgId += " and w.start_time >= '" + json.get("start_time").toString() + "' ";
                    start_time = json.get("start_time").toString();
                }

            }
            String end_time = "";
            if(json.has("end_time")){
                if(null != json.get("end_time").toString() && "" != json.get("end_time").toString()){
                    sqlOrgId += " and w.end_time <= '" + json.get("end_time").toString() + "' ";
                    end_time = json.get("end_time").toString();
                }
            }

            if(json.has("organization_id")){
                if(null != json.get("organization_id").toString() && "" != json.get("organization_id").toString()){
                    sqlOrgId += " and w.organization_id = '" + json.get("organization_id").toString() + "' ";
                }
            }

            List<String[]> listOrg = this.getReportingTo(sqlOrgId);

            List<WorkWeekPlanSearchShowDTO> workWeekPlanSearchShowDTOList = new ArrayList<WorkWeekPlanSearchShowDTO>();

            if(listOrg.size() < 1){
                resultDTO.setStatus("90002");
                resultDTO.setDescritpion(warnSrc.delete_fail);
                resultDTO.setDescritpion("部门数据未上传");
            }else{

                for(int i = 0; i<listOrg.size();i++){
                    WorkWeekPlanSearchShowDTO weekPlanSearchShowDTO = new WorkWeekPlanSearchShowDTO();
                    weekPlanSearchShowDTO.setOrganization_name(listOrg.get(i)[2]);
                    weekPlanSearchShowDTO.setWorkWeekPlanDetialDTOList(getWWPDetial(listOrg.get(i)[1],start_time,end_time));
                    workWeekPlanSearchShowDTOList.add(weekPlanSearchShowDTO);
                }
            }

            if(workWeekPlanSearchShowDTOList.size() != 0){
                resultDTO.setStatus("90000");
                resultDTO.setDescritpion(warnSrc.get_success);
                resultDTO.setValues(workWeekPlanSearchShowDTOList);
            }
            else{
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion(warnSrc.get_fail);
            }

        } catch (JSONException e) {
            e.printStackTrace();
        }
        return resultDTO;
    }

    @Override
    public ResultDTO exportWorkWeekPlanDetail(String search) {

        ResultDTO resultDTO = new ResultDTO();
        String excelDownloadUrl = "";

        String pdfDownLoadUrl = "";

        List<String[]> seresult = new ArrayList<String[]>();

        List<ExportWeekPlanDetialDTO> result = new ArrayList<ExportWeekPlanDetialDTO>();

        try {
            JSONObject json = new JSONObject(search);

            String sql = "select o.organization_name,wd.week_date,wd.year_date,wd.am_plan,wd.pm_plan " +
                    "from work_week_plan w " +
                    "LEFT JOIN organization o " +
                    "  ON o.organization_id = w.organization_id " +
                    "LEFT JOIN work_week_plan_detail wd " +
                    "on wd.work_week_plan_id = w.work_week_plan_id " +
                    "where 1=1 ";

            if(json.has("start_time")){
                if(null != json.get("start_time").toString() && !json.get("start_time").toString().isEmpty() ){
                    sql += " and w.start_time >= '" + json.get("start_time").toString() + "' ";
                }
            }

            if(json.has("end_time")){
                if(null != json.get("end_time").toString() && !json.get("end_time").toString().isEmpty() ){
                    sql += " and w.end_time <= '" + json.get("end_time").toString() + "' ";
                }
            }

            if(json.has("organization_id")){
                if(null != json.get("organization_id").toString() && !json.get("organization_id").toString().isEmpty()){
                    sql += " and w.organization_id = '" + json.get("organization_id").toString() + "' ";
                }
            }

            sql += "GROUP BY o.organization_name,wd.year_date ";


            seresult = this.getReportingTo(sql);

            for(int i =0;i<seresult.size();i++){
                ExportWeekPlanDetialDTO exportWeekPlanDetialDTO = new ExportWeekPlanDetialDTO();

                exportWeekPlanDetialDTO.setOrganization_name(seresult.get(i)[1] == null? "" : seresult.get(i)[1]);
                exportWeekPlanDetialDTO.setWeek_date(seresult.get(i)[2] == null? "" : seresult.get(i)[2]);
//                exportWeekPlanDetialDTO.setStart_time(seresult.get(i)[3] == null? "" : seresult.get(i)[3]);
//                exportWeekPlanDetialDTO.setEnd_time(seresult.get(i)[4] == null? "" : seresult.get(i)[4]);
                exportWeekPlanDetialDTO.setYear_date(seresult.get(i)[3] == null? "" : seresult.get(i)[3]);
                exportWeekPlanDetialDTO.setAm_plan(seresult.get(i)[4] == null? "" : seresult.get(i)[4]);
                exportWeekPlanDetialDTO.setPm_plan(seresult.get(i)[5] == null? "" : seresult.get(i)[5]);

                result.add(exportWeekPlanDetialDTO);
            }

            String[] headers = {"部门名称", "星期", "时间", "早上计划", "下午计划" };
            String title = "部门周计划";
            String filename = "部门周计划表";
            ExportExcel<ExportWeekPlanDetialDTO> exportExcel = new ExportExcel<ExportWeekPlanDetialDTO>();
            excelDownloadUrl = exportExcel.createExcelFile(exportExcel, title, headers, result, filename);

            try {
                pdfDownLoadUrl = ExportPDF.createPDFFile(seresult, title, headers, filename);
            } catch (DocumentException e) {
                e.printStackTrace();
            }

        } catch (JSONException e) {
            e.printStackTrace();
        }

        if(result.size() > 0){
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(pdfDownLoadUrl);
            resultDTO.setStr(excelDownloadUrl);
        }
        else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.get_fail);
        }

        return resultDTO;
    }

    //根据组织id查询工作计划详情
    private List<WorkWeekPlanDetialDTO> getWWPDetial(String orgId,String start_time,String end_time){
        List<String[]> result = new ArrayList<String[]>();
        List<WorkWeekPlanDetialDTO> sresult = new ArrayList<WorkWeekPlanDetialDTO>();

        String sql_w = "";
        if(null!=start_time&&!"".equals(start_time)){
            sql_w += " and wd.year_date >= '" + start_time + "' ";
        }
        if(null!=end_time&&!"".equals(end_time)){
            sql_w += " and wd.year_date <= '" + end_time + "' ";
        }
        String sql = "select wd.week_date,wd.am_plan,wd.pm_plan,wd.year_date " +
                "from work_week_plan w " +
                "LEFT JOIN organization o " +
                "ON o.organization_id = w.organization_id " +
                "LEFT JOIN work_week_plan_detail wd " +
                "on wd.work_week_plan_id = w.work_week_plan_id " +
                "where 1=1 " +
                "and o.organization_id = '" + orgId + "' " + sql_w +
                "ORDER BY wd.year_date;";
        result = this.getReportingTo(sql);

        for(int i =0;i < result.size();i++){
            WorkWeekPlanDetialDTO workWeekPlanDetialDTO = new WorkWeekPlanDetialDTO();
            workWeekPlanDetialDTO.setWeek_date(result.get(i)[1]);
            workWeekPlanDetialDTO.setAm_plan(result.get(i)[2]);
            workWeekPlanDetialDTO.setPm_plan(result.get(i)[3]);
            workWeekPlanDetialDTO.setYear_date(result.get(i)[4]);
            sresult.add(workWeekPlanDetialDTO);
        }

        return sresult;
    }
}
